﻿using Sustainalytics.ClientDW.Entities;
using System;
using System.Collections.Generic;

namespace Sustainalytics.ClientDW.Synchronization.SyncCarbonData
{
    public class CarbonDataSqlRepository
    {
        private readonly CarbonDataSqlDatabase _sqlDatabase;

        protected CarbonDataSqlRepository(CarbonDataSqlDatabase sqlDatabase)
        {
            _sqlDatabase = sqlDatabase;
        }

        public static CarbonDataSqlRepository GetInstance(CarbonDataSqlDatabase sqlDatabase)
        {
            return new CarbonDataSqlRepository(sqlDatabase);
        }

        public List<CarbonData> GetCarbonData()
        {
            var carbonData = new List<CarbonData>();

            using (var reader = _sqlDatabase.GetCarbonData())
            {
                if (reader == null) return carbonData;

                while (reader.Read())
                {
                    var emission = new CarbonEmissions
                    {
                        TotalRevenue = reader["Carbon_Total_Revenue"] as decimal?,
                        Scope1 = reader["Scope_1"] as decimal?,
                        Scope2 = reader["Scope_2"] as decimal?,
                        Total = reader["Total"] as decimal?,
                        Source = reader["Reported_Estimated"] as string,
                        CarbonIntensity = reader["Carbon_Intensity"] as decimal?,
                        CarbonIntensityPercentileRank = reader["Carbon_Intensity_Percentile_Rank"] as decimal?,
                        CarbonIntensityGroup = reader["Carbon_Intensity_Group"] as string,
                        FinancialYear = reader["Financial_Year"] as int?
                    };

                    var riskRating = new CarbonRiskRating
                    {
                        RiskScore = reader["Carbon_Risk_Score"] as decimal?,
                        ExposureScore = reader["Exposure_Score"] as decimal?,
                        ManagementScore = reader["Management_Score"] as decimal?,
                        RiskCategory = reader["Carbon_Risk_Category"] as string,
                        OperationsRiskScore = reader["Operations_Risk_Score"] as decimal?,
                        OperationsExposureScore = reader["Operations_Exposure_Score"] as decimal?,
                        OperationsManagementScore = reader["Operations_Management_Score"] as decimal?,
                        ProductsAndServicesRiskScore = reader["Products_Services_Risk_Score"] as decimal?,
                        ProductsAndServicesExposureScore = reader["Products_Services_Exposure_Score"] as decimal?,
                        ProductsAndServicesManagementScore = reader["Products_Services_Management_Score"] as decimal?,
                        OverallManagedRiskScore = reader["Overall_Managed_Risk_Score"] as decimal?,
                        OperationsManagedRiskScore = reader["Operations_Managed_Risk_Score"] as decimal?,
                        ProductsAndServicesManagedRiskScore = reader["Products_and_Services_Managed_Risk_Score"] as decimal?,
                        OverallManageableRiskScore = reader["Overall_Manageable_Risk_Score"] as decimal?,
                        OperationsManageableRiskScore = reader["Operations_Manageable_Risk_Score"] as decimal?,
                        ProductsAndServicesManageableRiskScore = reader["Products_and_Services_Manageable_Risk_Score"] as decimal?,
                        OperationsDefaultExposureScore = reader["Carbon_Operations_Default_Exposure_Score"] as decimal?,
                        ProductsAndServicesDefaultExposureScore = reader["Carbon_Products_and_Services_Default_Exposure_Score"] as decimal?
                    };

                    var fossilFuel = new CarbonFossilFuel
                    {
                        ThermalCoalExtraction = new LevelOfInvolvement
                        {
                            Id = reader["Thermal_Coal_Extraction_Id"] as int?,
                            Range = reader["Thermal_Coal_Extraction"] as string
                        },
                        ThermalCoalPowerGeneration = new LevelOfInvolvement
                        {
                            Id = reader["Thermal_Coal_Power_Generation_Id"] as int?,
                            Range = reader["Thermal_Coal_Power_Generation"] as string
                        },
                        OilSandsExtraction = new LevelOfInvolvement
                        {
                            Id = reader["Oil_Sands_Extraction_Id"] as int?,
                            Range = reader["Oil_Sands_Extraction"] as string,
                        },
                        ArcticOilAndGasExplorationInvolvement = reader["Arctic_Oil_and_Gas_Exploration_Involvement"] as string,
                        OilGasGeneration = new LevelOfInvolvement
                        {
                            Id = reader["Oil_Gas_Generation_Id"] as int?,
                            Range = reader["Oil_Gas_Generation"] as string
                        },
                        OilGasProduction = new LevelOfInvolvement
                        {
                            Id = reader["Oil_Gas_Production_Id"] as int?,
                            Range = reader["Oil_Gas_Production"] as string
                        },
                        OilGasProductsServices = new LevelOfInvolvement
                        {
                            Id = reader["Oil_Gas_Products_Services_Id"] as int?,
                            Range = reader["Oil_Gas_Products_Services"] as string
                        },
                        FossilFuelInvolvement = new LevelOfInvolvement
                        {
                            Id = reader["Fossil_Fuel_Involvement_Id"] as int?,
                            Range = reader["Fossil_Fuel_Involvement_Range"] as string
                        }
                    };

                    var solutions = new CarbonSolutions
                    {
                        RenewableEnergyProduction = new LevelOfInvolvement
                        {
                            Id = reader["Renewable_Energy_Production_Id"] as int?,
                            Range = reader["Renewable_Energy_Production"] as string
                        },
                        RenewableEnergySupportingProductServices = new LevelOfInvolvement
                        {
                            Id = reader["Renewable_Energy_Supporting_Product_Services_Id"] as int?,
                            Range = reader["Renewable_Energy_Supporting_Product_Services"] as string
                        },
                        GreenTransportation = new LevelOfInvolvement
                        {
                            Id = reader["Green_Transportation_Id"] as int?,
                            Range = reader["Green_Transportation"] as string
                        },
                        Involvement = new LevelOfInvolvement
                        {
                            Id = reader["Carbon_Solutions_Level_of_Involvement_Id"] as int?,
                            Range = reader["Carbon_Solutions_Level_of_Involvement_Range"] as string
                        }
                    };

                    var strandedAssets = new CarbonStrandedAssets
                    {
                        ExposureScore = reader["Stranded_Assets_Exposure_Score"] as decimal?,
                        OilAndGasReserves = reader["Oil_and_Gas_Reserves"] as decimal?,
                        OilAndGasProduction = reader["Oil_and_Gas_Production"] as decimal?,
                        WeightedLifecycleIntensityScore = reader["Carbon_Weighted_Lifecycle_Intensity_Score"] as decimal?,
                        WeightedLifecycleIntensity = reader["Carbon_Weighted_Lifecycle_Intensity_Estimate_kgCO2e_BOE"] as decimal?,
                        ReserveLifecycleIntensity = reader["Carbon_Reserve_Lifecycle_Intensity_Estimate_kgCO2e_BOE"] as decimal?,
                        ProductionLifecycleIntensity = reader["Carbon_Production_Lifecycle_Intensity_Estimate_kgCO2e_BOE"] as decimal?,
                        TotalOilAndGasReserves = reader["Carbon_Total_Oil_and_Gas_Reserves_MMBOE"] as decimal?,
                        TotalCrudeOilReserves = reader["Carbon_Total_Crude_Oil_Reserves_MMBOE"] as decimal?,
                        TotalBitumenAndSyntheticCrudeOilReserves = reader["Carbon_Total_Bitumen_and_SCO_Reserves_MMBOE"] as decimal?,
                        TotalNaturalGasReserves = reader["Carbon_Total_Natural_Gas_Reserves_MMBOE"] as decimal?,
                        TotalNaturalGasLiquidsReserves = reader["Carbon_Total_NGL_Reserves_MMBOE"] as decimal?,
                        TotalOilAndGasProduction = reader["Carbon_Total_Oil_and_Gas_Production_MMBOE"] as decimal?,
                        TotalOilProduction = reader["Carbon_Total_Oil_Production_MMBOE"] as decimal?,
                        TotalBitumenAndSyntheticCrudeOilProduction = reader["Carbon_Total_Bitumen_and_SCO_Production_MMBOE"] as decimal?,
                        TotalNaturalGasProduction = reader["Carbon_Total_Natural_Gas_Production_MMBOE"] as decimal?,
                        TotalNaturalGasLiquidProduction = reader["Carbon_Total_NGL_Production_MMBOE"] as decimal?,
                        WeightedHighCostProduction = reader["Carbon_Weighted_High_Cost_Production"] as decimal?
                    };

                    var company = new CarbonData
                    {
                        Id = (Guid)reader["CompanyId"],
                        Emissions = emission,
                        RiskRating = riskRating,
                        FossilFuel = fossilFuel,
                        Solutions = solutions,
                        StrandedAssets = strandedAssets
                    };

                    carbonData.Add(company);
                }
            }

            return carbonData;
        }
    }
}
